# -*- coding: utf-8 -*-
# file: core.py
# @Author: zlw


import time
# import re
from db.db_handle import SchoolConnect, UserdbConnect
from lib.common import logger

userdb = UserdbConnect()
school = SchoolConnect()


def register():
    print('执行注册功能'.center(30, '*'))

    count = 0
    while count < 5:
        userdb.username = input('输入用户名>>>').strip()
        userdb.select_username()
        res = userdb.cursor.fetchone()
        if res:
            count += 1
            print('用户名已存在!')
            continue
        userdb.passwd = input('输入密码>>>').strip()
        userdb.add_data()

        print(f'注册成功\n用户名:{userdb.username}  密码:{userdb.passwd}')
        logger(f'用户 {userdb.username} 注册了学生管理系统.')
        break


def login():
    print('执行登录功能'.center(30, '*'))

    count = 0
    while count < 3:
        userdb.username = input('输入用户名>>>').strip()
        userdb.passwd = input('输入密码>>>').strip()

        userdb.select_data()
        res = userdb.cursor.fetchone()

        if not res:
            count += 1
            print(f'该用户不存在, 重新输入.您还有{3 - count}次机会!')
            continue

        print('登录成功')
        logger(f'用户 {userdb.username} 登录了学生管理系统.')
        break


def add_stu():
    print('执行添加学生信息功能'.center(30, '*'))

    while True:
        name = input('输入学生姓名>>>').strip()
        gender = input('输入学生性别>>>').strip()
        age = int(input('输入学生年龄>>>').strip())

        school.cursor.execute("select name, gender, age from tb_student")
        res = school.cursor.fetchall()

        for n, g, a in res:
            if name == n and gender == g and age == a:
                print('此学生已存在.')
                break

            birthday = input('输入出生日期>>>').strip()
            tel = input('输入电话号码>>>').strip()
            addr = input('输入住址>>>').strip()

            school.cursor.execute(f"\
            insert into tb_student(name, gender, age, birthday, tel, addr)\
            values('{name}','{gender}', '{age}', '{birthday}', '{tel}', '{addr}')")

            school.conn.commit()

            print('学生信息添加成功')
            logger("成功添加一名学生信息")

            answer = input('是否继续添加？y/n: ').strip()
            if answer != 'y':
                return


def delete_stu():
    print('执行删除学生信息功能'.center(30, '*'))

    while True:
        name = input('输入学生姓名>>>').strip()
        school.cursor.execute(f"select * from tb_student where name='{name}'")
        res = school.cursor.fetchall()
        if not res:
            print('此学生不存在!')
        school.cursor.execute(f"delete from tb_student where name='{name}'")
        school.conn.commit()
        print(f'学生{name}已删除')
        logger(f'学生{name}已被删除')

        answer = input('是否继续删除其他学生?>>>').strip()
        if answer == 'y':
            continue
        else:
            school.close_db()
            return


def modify_stu():
    print('执行修改学生信息功能'.center(30, '*'))

    while True:
        name = input('输入学生姓名>>>').strip()
        school.cursor.execute(f"select * from tb_student where name='{name}'")
        res = school.cursor.fetchone()
        if not res:
            print('此学生不存在!')

        gender = input('输入学生性别>>>').strip()
        age = int(input('输入学生年龄>>>').strip())
        birthday = input('输入出生日期>>>').strip()
        tel = input('输入电话号码>>>').strip()
        addr = input('输入住址>>>').strip()

        school.cursor.execute(f"update tb_student set gender='{gender}' where name='{name}'")
        school.conn.commit()
        school.cursor.execute(f"update tb_student set age='{age}' where name='{name}'")
        school.conn.commit()
        school.cursor.execute(f"update tb_student set birthday='{birthday}' where name='{name}'")
        school.conn.commit()
        school.cursor.execute(f"update tb_student set tel='{tel}' where name='{name}'")
        school.conn.commit()
        school.cursor.execute(f"update tb_student set addr='{addr}' where name='{name}'")
        school.conn.commit()
        print(f"学生{name}信息已修改完成")
        logger(f"学生{name}信息已被修改!")

        answer = input('是否继续修改其他学生?>>>').strip()
        if answer != 'y':
            return


def search_stu():
    print('执行查询学生信息功能'.center(30, '*'))

    dic = {
        '1': ('按学号', 'id'),
        '2': ('按姓名', 'name'),
        '3': ('按性别', 'gender'),
        '4': ('按年龄', 'age'),
        '5': ('按生日', 'birthday'),
        '6': ('按电话', 'tel'),
        '7': ('按地址', 'addr')
    }
    while True:
        for k, v in dic.items():
            print('\t', k, v[0])
        opt = input('输入查询方式序号>>>').strip()
        if opt not in dic:
            print('此查询方式不存在!')
            continue

        if opt == '1':
            input_id = int(input('输入学生学号>>>').strip())
            school.cursor.execute(f"select * from tb_student where id='{input_id}'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)
        elif opt == '2':
            input_name = input('输入学生姓名>>>').strip()
            school.cursor.execute(f"select * from tb_student where name like '{input_name}%'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)
        elif opt == '3':
            input_gender = input('输入学生性别>>>').strip()
            school.cursor.execute(f"select * from tb_student where gender='{input_gender}'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)
        elif opt == '4':
            input_age = input('输入学生年龄>>>').strip()
            school.cursor.execute(f"select * from tb_student where age='{input_age}'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)
        elif opt == '5':
            input_birthday = input('输入学生出生日期>>>').strip()
            school.cursor.execute(f"select * from tb_student where birthday like '{input_birthday}'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)
        elif opt == '6':
            input_tel = input('输入学生电话>>>').strip()
            school.cursor.execute(f"select * from tb_student where tel like '{input_tel}%'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)
        else:
            input_addr = input('输入学生地址>>>').strip()
            school.cursor.execute(f"select * from tb_student where addr like '%{input_addr}%'")
            res = school.cursor.fetchall()
            if not res:
                print('此学生不存在!')
            show(res)

        answer = input('是否继续查询其他学生?>>>').strip()
        if answer != 'y':
            return


def show_all_stu():
    print('执行显示所有学生信息功能'.center(30, '*'))

    school.cursor.execute("select * from tb_student")
    res = school.cursor.fetchall()
    show(res)


def sort_stu():
    print('执行所有学生信息排序功能'.center(30, '*'))
    pass


def show(res):
    print("学号\t姓名\t\t性别\t年龄\t出生日期\t\t\t电话\t\t\t住址")
    for i in res:
        for j in i:
            print(j, end='\t\t')
        print()
    print('-' * 30)
    time.sleep(2)


func_dic = {
        '0': ('退出管理系统', exit),
        '1': ('登录管理系统', login),
        '2': ('注册管理系统', register),
        '3': ('添加学生信息', add_stu),
        '4': ('删除学生信息', delete_stu),
        '5': ('修改学生信息', modify_stu),
        '6': ('查询学生信息', search_stu),
        '7': ('显示学生信息', show_all_stu),
        '8': ('学生信息排序', sort_stu)
}


def main():
    while True:
        print('学生管理系统'.center(28, '='))
        for k, v in func_dic.items():
            print('\t\t', k, v[0])
        print('-' * 32)

        opt = input('输入功能序号>>>').strip()

        if opt not in func_dic:
            print('\033[33m输入有误，重新输入!\033[0m')
            continue

        func_dic[opt][1]()



